package com.tmpt.others;

import java.util.List;

import org.hibernate.QueryException;
import org.hibernate.dialect.SQLServerDialect;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.engine.spi.Mapping;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;

public class MsSQLServerDialect extends SQLServerDialect{
		/** 
	     *  
	     * 是否需要绑定limit参数? 
	     *  
	     * 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数 
	     */  
	  
	    private ThreadLocal<Boolean> supportsVariableLimit = new ThreadLocal<Boolean>();  
	    public MsSQLServerDialect() {  
	        registerFunction("bitand", new BitAndFunction());  
	        registerFunction("bitxor", new BitXorFunction());  
	        registerFunction("bitor", new BitOrFunction());  
	        setSupportsVariableLimit(false);  
	    }  
	  
	    /** 
	     *  
	     * <p> 
	     * 设置是否先绑定limit参数。 
	     * </p> 
	     *  
	     * @param first 
	     */  
	  
	    private void setSupportsVariableLimit(boolean first) {  
	        this.supportsVariableLimit.set(Boolean.valueOf(first));  
	    }  
	  
	    /** 
	     *  
	     * <p> 
	     * 获取sql中select子句位置。 
	     * </p> 
	     *  
	     * @param sql 
	     *  
	     * @return int 
	     */  
	    protected static int getSqlAfterSelectInsertPoint(String sql) {  
	        int selectIndex = sql.toLowerCase().indexOf("select");  
	  
	        int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");  
	  
	        return selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6);  
	    }  
	  
	    public boolean supportsLimitOffset() {  
	        return true;  
	    }  
	  
	    /* 
	     * Hibernate在获得Limit String(已添加了limit子句)后，如果此方法返回true， 
	     *  
	     * 则会添加额外的参数值(ROW_NUMBER()范围)(策略可能是这样：有offset设置两个参数值，没有设置一个参数值) 
	     */  
	    public boolean supportsVariableLimit() {  
	        return ((Boolean) this.supportsVariableLimit.get()).booleanValue();  
	    }  
	  
	    public boolean useMaxForLimit() {  
	        return true;  
	    }  
	    /** 
	     * 首页top，以后用ROW_NUMBER 
	     */  
	    public String getLimitString(String query, int offset, int limit) {  
	        setSupportsVariableLimit(offset > 0);  
	  
	        if (offset == 0) {  
	            return new StringBuffer(query.length() + 8).append(query).insert(  
	                    getSqlAfterSelectInsertPoint(query), " top " + limit)  
	                    .toString();  
	        }  
	  
	        return getLimitString(query, offset > 0);  
	    }  
	      
	    public String getLimitString(String sql, boolean hasOffset) {  
	        int orderByIndex = sql.toLowerCase().lastIndexOf("order by");  
	  
	        if (orderByIndex <= 0) {  
	            throw new UnsupportedOperationException(  
	                    "must specify 'order by' statement to support limit operation with offset in sql server 2005");  
	        }  
	  
	        String sqlOrderBy = sql.substring(orderByIndex + 8);  
	  
	        String sqlRemoveOrderBy = sql.substring(0, orderByIndex);  
	  
	        int insertPoint = getSqlAfterSelectInsertPoint(sql);  
	        return new StringBuffer(sql.length() + 100)  
	                .append("with tempPagination as(")  
	                .append(sqlRemoveOrderBy)  
	                .insert(  
	                        insertPoint + 23,  
	                        " ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy  
	                                + ") as RowNumber,")  
	                .append(  
	                        ") select * from tempPagination where RowNumber>?  and RowNumber<=?")  
	                .toString();  
	    }  
}  
class BitAndFunction implements SQLFunction {  
	    public Type getReturnType(Type type, Mapping mapping) {
	        return StandardBasicTypes.INTEGER;
	    }  
	  
	    public boolean hasArguments() {  
	        return true;  
	    }  
	  
	    public boolean hasParenthesesIfNoArguments() {  
	        return true;  
	    }  
	  
	    public String render(Type type, List args, SessionFactoryImplementor factory)
	            throws QueryException {  
	        if (args.size() != 2) {  
	            throw new IllegalArgumentException(  
	                    "BitAndFunction requires 2 arguments!");  
	        }  
	        return args.get(0).toString() + " & " + args.get(1).toString();  
	    }  
	}

	class BitOrFunction implements SQLFunction {
	    public Type getReturnType(Type type, Mapping mapping) {  
	        return StandardBasicTypes.INTEGER;
	    }  
	    public boolean hasArguments() {  
	        return true;  
	    }  
	    public boolean hasParenthesesIfNoArguments() {  
	        return true;  
	    }  
	    public String render(Type type, List args, SessionFactoryImplementor factory)  
	            throws QueryException {  
	        if (args.size() != 2) {  
	            throw new IllegalArgumentException(  
	                    "BitOrFunction requires 2 arguments!");  
	        }  
	        return args.get(0).toString() + " | " + args.get(1).toString();  
	    }  
	   
}  

class BitXorFunction implements SQLFunction {  
	 public Type getReturnType(Type type, Mapping mapping) {  
	        return StandardBasicTypes.INTEGER;
	    }  
	    public boolean hasArguments() {  
	        return true;  
	    }  
	    public boolean hasParenthesesIfNoArguments() {  
	        return true;  
	    }  
	    public String render(Type type, List args, SessionFactoryImplementor factory)  
	            throws QueryException {  
	        if (args.size() != 2) {  
	            throw new IllegalArgumentException(  
	                    "BitXorFunction requires 2 arguments!");  
	        }  
	        return args.get(0).toString() + " ^ " + args.get(1).toString();  
	    }
	    
}
	

